﻿using System;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Data;
using System.Collections.Generic;
using System.Net;
using System.Data.OleDb;

namespace HY.Utility.ImportExport
{
    public class ExcelHelper : IDisposable
    {
        private string m_fileName = null; //文件名
        private IWorkbook m_workbook = null;
        private FileStream m_fs = null;
        private bool m_disposed;

        public ExcelHelper(string fileName)
        {
            this.m_fileName = fileName;
            m_disposed = false;
        }

       

        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            ISheet sheet = null;

            m_fs = new FileStream(m_fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (m_fileName.IndexOf(".xlsx") > 0) // 2007版本
                m_workbook = new XSSFWorkbook();
            else if (m_fileName.IndexOf(".xls") > 0) // 2003版本
                m_workbook = new HSSFWorkbook();

            try
            {
                if (m_workbook != null)
                {
                    sheet = m_workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }

                if (isColumnWritten == true) //写入DataTable的列名
                {
                    IRow row = sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }

                for (i = 0; i < data.Rows.Count; ++i)
                {
                    IRow row = sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    ++count;
                }
                m_workbook.Write(m_fs); //写入到excel
                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return -1;
            }
        }

        public bool DataTablesToExcel(DataTable[] datas,List<MergeInfo> mergeList=null)
        {
            bool output = false;
            ISheet sheet = null;

            m_fs = new FileStream(m_fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (m_fileName.IndexOf(".xlsx") > 0) // 2007版本
                m_workbook = new XSSFWorkbook();
            else if (m_fileName.IndexOf(".xls") > 0) // 2003版本
                m_workbook = new HSSFWorkbook();

            try
            {
                if (m_workbook != null)
                {
                    List<ISheet> sheetlist = new List<ISheet>();
                    foreach (var dt in datas)
                    {
                        sheet = m_workbook.CreateSheet(dt.TableName);
                        sheetlist.Add(sheet);
                        for (int i= 0;i< dt.Rows.Count;++i)
                        {
                            IRow row = sheet.CreateRow(i);
                            for (var j = 0; j < dt.Columns.Count; ++j)
                                row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                        }
                    }
                    if (mergeList != null)
                        mergeList.ForEach(s => {
                            sheet = sheetlist[s.SheetIndex];
                            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(s.StartCell.Y, s.EndCell.Y, s.StartCell.X, s.EndCell.X));
                            sheet.GetRow(s.StartCell.Y).GetCell(s.StartCell.X).SetCellValue(s.Text);
                            var style = m_workbook.CreateCellStyle();
                            style.Alignment = HorizontalAlignment.Center;
                            sheet.GetRow(s.StartCell.Y).GetCell(s.StartCell.X).CellStyle = style;
                        });
                    m_workbook.Write(m_fs); //写入到excel
                }
            }
            catch(Exception e)
            {
                return false;
            }
            return output;
        }

        public delegate int WriteCellProc(ISheet sheet);

        public void SetCells(ISheet sheet, int rownum, object[] values)
        {
            IRow row = sheet.CreateRow(rownum);
            for (int i = 0; i < values.Length; ++i)
            {
                row.CreateCell(i).SetCellValue(Convert.ToString(values[i]));
            }
        }


        private string GetFirstTableName(OleDbConnection con, int sheetindex)
        {
            DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (sheetindex >= dt.Rows.Count)
                throw new Exception("不存在的表索引");
            string tableName = dt.Rows[sheetindex][2].ToString().Trim();
            return tableName;
        }

        public DataTable ExcelToDataTableWithOleDb(int sheetindex = 0)
        {
            DataSet ds = new DataSet();
            string strConn = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{m_fileName}';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                string strExcel = "";
                strExcel = $"select * from [{GetFirstTableName(conn, sheetindex)}]";
                using (OleDbDataAdapter da = new OleDbDataAdapter(strExcel, strConn))
                    da.Fill(ds);
            }
            //var dt = ds.Tables[0];//9 index
            return ds.Tables[0];
        }

        //public int ToExcel(string sheetName, WriteCellProc proc, params string[] columnheads)
        //{
        //    int i = 0;
        //    int j = 0;
        //    int count = 0;
        //    ISheet sheet = null;

        //    m_fs = new FileStream(m_fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
        //    if (m_fileName.IndexOf(".xlsx") > 0) // 2007版本
        //        m_workbook = new XSSFWorkbook();
        //    else if (m_fileName.IndexOf(".xls") > 0) // 2003版本
        //        m_workbook = new HSSFWorkbook();

        //    try
        //    {
        //        if (m_workbook != null)
        //        {
        //            sheet = m_workbook.CreateSheet(sheetName);
        //        }
        //        else
        //        {
        //            return -1;
        //        }
        //        if (columnheads != null)
        //        {
        //            foreach (string s in columnheads)
        //            {
        //                IRow row = sheet.CreateRow(0);
        //                for (j = 0; j < columnheads.Length; ++j)
        //                {
        //                    row.CreateCell(j).SetCellValue(columnheads[j]);
        //                }
        //                count = 1;
        //            }
        //        }

        //        if (proc == null)
        //            throw new Exception("error");

        //        count = proc(sheet);
        //        //if (isColumnWritten == true) //写入DataTable的列名
        //        //{
        //        //    IRow row = sheet.CreateRow(0);
        //        //    for (j = 0; j < data.Columns.Count; ++j)
        //        //    {
        //        //        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
        //        //    }
        //        //    count = 1;
        //        //}
        //        //else
        //        //{
        //        //    count = 0;
        //        //}

        //        //for (i = 0; i < data.Rows.Count; ++i)
        //        //{
        //        //    IRow row = sheet.CreateRow(count);
        //        //    for (j = 0; j < data.Columns.Count; ++j)
        //        //    {
        //        //        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
        //        //    }
        //        //    ++count;
        //        //}
        //        m_workbook.Write(m_fs); //写入到excel
        //        return count;
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine("Exception: " + ex.Message);
        //        return -1;
        //    }
        //}

        //public string[] GetSheetNames()
        //{
        //    try
        //    {
        //        using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
        //        {
        //            if (fileName.IndexOf(".xlsx") > 0) // 2007版本
        //                workbook = new XSSFWorkbook(fs);
        //            else if (fileName.IndexOf(".xls") > 0) // 2003版本
        //                workbook = new HSSFWorkbook(fs);

        //            List<string> sheets = new List<string>();
        //            for (int i = 0; i < workbook.Count; ++i)
        //            {
        //                sheets.Add(workbook.GetSheetName(i));
        //            }
        //            return sheets.ToArray();
        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        MessageBox.Show(e.Message);
        //        return null;
        //    }
        //}
        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, int FirstRowColumnIndex)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {
                m_fs = new FileStream(m_fileName, FileMode.Open, FileAccess.Read);
                if (m_fileName.IndexOf(".xlsx") > 0) // 2007版本
                    m_workbook = new XSSFWorkbook(m_fs);
                else if (m_fileName.IndexOf(".xls") > 0) // 2003版本
                    m_workbook = new HSSFWorkbook(m_fs);

                if (sheetName != null)
                {
                    sheet = m_workbook.GetSheet(sheetName);
                }
                else
                {
                    sheet = m_workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    for (int i = 0; i < FirstRowColumnIndex; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row != null)
                            sheet.RemoveRow(row);
                    }

                    int rowindex = FirstRowColumnIndex;
                    IRow firstRow = sheet.GetRow(rowindex);
                    int cellCount = firstRow.LastCellNum;

                    if (isFirstRowColumn)
                    {
                        int index = 1;
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            var colname = firstRow.GetCell(i).ToString();
                            if (data.Columns.IndexOf(colname) != -1)
                                colname = "自定义列" + (index++);
                            DataColumn column = new DataColumn(colname);
                            data.Columns.Add(column);
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                        //IRow firstRow = sheet.GetRow(FirstRowColumnIndex);
                        //cellCount = firstRow.LastCellNum;
                        //startRow = FirstRowColumnIndex;
                    }



                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (j >= 0)
                            {
                                if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                {
                                    string text = row.GetCell(j).ToString();
                                    if (text.IndexOf("%") != -1)
                                    {
                                        if (text[0] == '.')
                                            text = "0" + text;
                                    }
                                    dataRow[j] = text;
                                }
                            }
                        }
                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                m_fs.Close();
                m_fs.Dispose();
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        public Dictionary<string,string> ExportExcelReplaceColumnNameDic { get; set; }

        public void DataTableToExcel(DataTable dt, List<int> pic_indexs)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = null;
            NPOI.SS.UserModel.ISheet sheet = null;
            NPOI.SS.UserModel.IRow headerRow = null;
            try
            {
                m_fs = File.Create(m_fileName);

                workbook = new HSSFWorkbook();
                sheet = workbook.CreateSheet();
                //sheet.DefaultRowHeight = ;
                sheet.DefaultColumnWidth = 8;
                headerRow = sheet.CreateRow(0);

                //headerRow.Height = 100 * 50;
                //设置7列宽为100
                //sheet.SetColumnWidth(7, 100);

                //加标题
                for (int i = 0; i < dt.Columns.Count; ++i)
                {
                    if (ExportExcelReplaceColumnNameDic != null)
                        if (ExportExcelReplaceColumnNameDic.ContainsKey(dt.Columns[i].ColumnName))
                        {
                            headerRow.CreateCell(i).SetCellValue(ExportExcelReplaceColumnNameDic[dt.Columns[i].ColumnName]);
                            continue;
                        }
                    headerRow.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }

                //string picurl = "https://img.alicdn.com/bao/uploaded/i3/TB18InbHFXXXXbHaXXXXXXXXXXX_!!0-item_pic.jpg_430x430q90.jpg";
                //int rowIndex = 1;
                for (int i = 0; i < dt.Rows.Count; ++i)
                {
                    int rowIndex = i + 1;
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    dataRow.Height = 280 * 3;
                    for (int j = 0; j < dt.Columns.Count; ++j)
                    {
                        string value = Convert.ToString(dt.Rows[i].ItemArray[j]);
                        if (pic_indexs.IndexOf(j) == -1)
                        {
                            dataRow.CreateCell(j).SetCellValue(value);
                        }
                        else
                        {
                            string pic_url = value;
                            if (string.IsNullOrEmpty(pic_url))
                                continue;
                            AddPieChart(sheet, workbook, pic_url, rowIndex, j);
                        }
                    }
                }
                //AddPieChart(sheet, workbook, picurl, rowIndex, 1);
                //AddPieChart(sheet, workbook, picurl, 2, 1);

                workbook.Write(m_fs);
            }
            finally
            {
                //ms.Flush();
                //ms.Position = 0;
                //string filename = "d:\\123.xls";
                //using (FileStream fs = File.Create(filename))
                //{
                //    byte[] buffer = ms.GetBuffer();
                //    fs.Write(buffer, 0, buffer.Length);
                //}
                //System.Diagnostics.Process.Start(filename);
                if (m_fs != null)
                {
                    m_fs.Close();
                    m_fs.Dispose();
                }
                sheet = null;
                headerRow = null;
                workbook = null;
            }
        }

        ///
        /// 向sheet插入图片
        ///
        ///
        ///
        private void AddPieChart(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col)
        {
            try
            {
                //add picture data to this workbook.
                //string path = Server.MapPath("~/html/");
                //if (fileurl.Contains("/"))
                //{
                //    path += fileurl.Substring(fileurl.IndexOf('/'));
                //}
                string path = AppDomain.CurrentDomain.BaseDirectory + "Temp" + DateTime.Now.Ticks + ".jpg";
                using (WebClient client = new WebClient())
                {
                    client.DownloadFile(fileurl, path);
                }
                string FileName = path;
                byte[] bytes = System.IO.File.ReadAllBytes(FileName);
                if (!string.IsNullOrEmpty(FileName))
                {
                    int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
                    HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1, 1, col, row, col + 1, row + 1);
                    //##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50
                    HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                    //pict.Resize();//这句话一定不要，这是用图片原始大小来显示
                }
                File.Delete(path);
            }
            catch (Exception ex)
            {
            }
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this.m_disposed)
            {
                if (disposing)
                {
                    if (m_fs != null)
                        m_fs.Close();
                }

                m_fs = null;
                m_disposed = true;
            }
        }
    }
}